﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;
using System.Data;
using DbAdministrator.Modals;

namespace DbAdministrator.Controllers
{
    [Authorize]
    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            ViewBag.Post = false;
            DbInfo info = new DbInfo();
            using (SqlConnection con = new SqlConnection(
                    "Data Source=10.132.42.205;Initial Catalog=master;User Id=sa;Password=sa@!#$"))
            {
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "select name from sys.databases";
                cmd.CommandType = System.Data.CommandType.Text;
                con.Open();
                var reader = cmd.ExecuteReader();
                List<string> db = new List<string>();
                while (reader.Read())
                {
                    db.Add(reader[0].ToString());
                }
                info.dblist = db;
            }
            using (SqlConnection con = new SqlConnection(
                    "Data Source=10.132.42.205;Initial Catalog=" + info.dblist[0] + ";User Id=sa;Password=sa@!#$"))
            {
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "select name from sys.tables";
                cmd.CommandType = System.Data.CommandType.Text;
                con.Open();
                var reader = cmd.ExecuteReader();
                List<string> tables = new List<string>();
                while (reader.Read())
                {
                    tables.Add(reader[0].ToString());
                }
                info.firsttables = tables;
            }
            ViewBag.Db = info;
            return View();
        }
        [HttpPost]
        public ActionResult index(string database, string databasename, string command)
        {
            ViewBag.Post = true;
            #region index
            DbInfo info = new DbInfo();
            using (SqlConnection con = new SqlConnection(
                    "Data Source=10.132.42.205;Initial Catalog=master;User Id=sa;Password=sa@!#$"))
            {
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "select name from sys.databases";
                cmd.CommandType = System.Data.CommandType.Text;
                con.Open();
                var reader = cmd.ExecuteReader();
                List<string> db = new List<string>();
                while (reader.Read())
                {
                    db.Add(reader[0].ToString());
                }
                info.dblist = db;
            }
            using (SqlConnection con = new SqlConnection(
                    "Data Source=10.132.42.205;Initial Catalog=" + info.dblist[0] + ";User Id=sa;Password=sa@!#$"))
            {
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "select name from sys.tables";
                cmd.CommandType = System.Data.CommandType.Text;
                con.Open();
                var reader = cmd.ExecuteReader();
                List<string> tables = new List<string>();
                while (reader.Read())
                {
                    tables.Add(reader[0].ToString());
                }
                info.firsttables = tables;
            }
            ViewBag.Db = info;
            #endregion
            ViewBag.database = database;
            ViewBag.databasename = databasename;
            ViewBag.command = command;
            if (database == "sqlserver")
            {
                using (SqlConnection con = new SqlConnection(
                    "Data Source=10.132.42.205;Initial Catalog=" + databasename + ";User Id=sa;Password=sa@!#$"))
                {
                    try
                    {
                        SqlCommand cmd = con.CreateCommand();
                        cmd.CommandText = command;
                        cmd.CommandType = System.Data.CommandType.Text;
                        if (command.Trim().ToLower().StartsWith("select"))
                        {
                            SqlDataAdapter da = new SqlDataAdapter(cmd);
                            DataSet ds = new DataSet();
                            con.Open();
                            da.Fill(ds);
                            ViewBag.ResultType = "table";
                            ViewBag.Result = ds.Tables[0];
                        }
                        else
                        {
                            con.Open();
                            ViewBag.Result = cmd.ExecuteNonQuery();
                        }
                    }
                    catch (Exception e)
                    {
                        ViewBag.Result = null;
                        ViewBag.Error = e.ToString();
                    }
                }
            }
            else
            {
                using (MySqlConnection con = new MySqlConnection(
                      "DataSource=10.132.42.205;DataBase=" + databasename + ";uid=root;pwd=root@!#$;characterset=utf8;"))
                {
                    try
                    {
                        MySqlCommand cmd = con.CreateCommand();
                        cmd.CommandText = command;
                        cmd.CommandType = System.Data.CommandType.Text;
                        if (command.Trim().ToLower().StartsWith("select") ||
                            command.Trim().ToLower().StartsWith("exec"))
                        {
                            MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                            DataSet ds = new DataSet();
                            con.Open();
                            da.Fill(ds);
                            ViewBag.ResultType = "table";
                            ViewBag.Result = ds.Tables[0];
                        }
                        else
                        {
                            con.Open();
                            ViewBag.Result = cmd.ExecuteNonQuery();
                        }
                    }
                    catch (Exception e)
                    {
                        ViewBag.Result = null;
                        ViewBag.Error = e.ToString();
                    }
                }
            }
            return View();
        }
        public string showtables(string database, string databaseName)
        {
            List<string> tables = new List<string>();
            if (database == "sqlserver")
            {
                using (SqlConnection con = new SqlConnection(
                        "Data Source=10.132.42.205;Initial Catalog=" + databaseName + ";User Id=sa;Password=sa@!#$"))
                {
                    SqlCommand cmd = con.CreateCommand();
                    cmd.CommandText = "select name from sys.tables";
                    cmd.CommandType = System.Data.CommandType.Text;
                    con.Open();
                    var reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        tables.Add(reader[0].ToString());
                    }
                }
            }
            else if (database == "mysql")
            {
                using (MySqlConnection con = new MySqlConnection(
                        "DataSource=10.132.42.205;DataBase=" + databaseName + ";uid=root;pwd=root@!#$;characterset=utf8;"))
                {
                    MySqlCommand cmd = con.CreateCommand();
                    cmd.CommandText = "show tables";
                    cmd.CommandType = System.Data.CommandType.Text;
                    con.Open();
                    var reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        tables.Add(reader[0].ToString());
                    }
                }
            }
            return tables.ToJson();
        }
        public string showdatabase(string database)
        {
            List<string> db = new List<string>();
            if (database == "sqlserver")
            {
                using (SqlConnection con = new SqlConnection(
                        "Data Source=10.132.42.205;Initial Catalog=master;User Id=sa;Password=sa@!#$"))
                {
                    SqlCommand cmd = con.CreateCommand();
                    cmd.CommandText = "select name from sys.databases";
                    cmd.CommandType = System.Data.CommandType.Text;
                    con.Open();
                    var reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        db.Add(reader[0].ToString());
                    }
                }
            }
            else if (database == "mysql")
            {
                using (MySqlConnection con = new MySqlConnection(
                        "DataSource=10.132.42.205;DataBase=mysql;uid=root;pwd=root@!#$;characterset=utf8;"))
                {
                    MySqlCommand cmd = con.CreateCommand();
                    cmd.CommandText = "show databases";
                    cmd.CommandType = System.Data.CommandType.Text;
                    con.Open();
                    var reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        db.Add(reader[0].ToString());
                    }
                }
            }
            return db.ToJson();
        }
        public ActionResult file()
        {
            return View();
        }
        [HttpPost]
        public ActionResult file(string path)
        {
            List<string> files = new List<string>();
            List<string> dicts = new List<string>();
            if (!String.IsNullOrEmpty(path))
            {
                string[] _dic = System.IO.Directory.GetDirectories(path);
                string[] _files = System.IO.Directory.GetFiles(path);
                if (_files != null)
                {
                    files = _files.Where(c=>!c.Contains(".config")).ToList();
                }
                if (_dic != null)
                {
                    dicts = _dic.ToList();
                }
            }
            ViewBag.Files = files;
            ViewBag.Dicts = dicts;
            return View();
        }
    }
}
